package es.udc.emarketapp.emarket.model.shipping.dao;

import es.udc.emarketapp.emarket.model.country.vo.CountryVO;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import es.udc.emarketapp.emarket.model.shipping.vo.ShippingVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public abstract class AbstractSQLShippingDAO implements SQLShippingDAO {

    protected AbstractSQLShippingDAO() {
    }

    public List<String> findCountriesBySeller(Connection connection,
            String idSeller, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT DISTINCT id_pais " +
                     "FROM envia WHERE id_vendedor LIKE ?";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idSeller);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects. 
            List<String> countries = new ArrayList<String>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    countries.add(resultSet.getString(i++));
                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return countries;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }


    }//findCountriesBySeller

    public Double findCost(Connection connection, String idSeller,
            String idCountry, Double weight) throws InternalErrorException {


        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement". 
            String queryString = "SELECT DISTINCT precio "
                    + "FROM envia where peso = (select min(peso) "
                    + "FROM envia where id_vendedor = ? and id_pais = ? "
                    + "and peso >= ?)";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            int i = 1;
            preparedStatement.setString(i++, idSeller);
            preparedStatement.setString(i++, idCountry);
            preparedStatement.setDouble(i++, weight);

            // Execute query. 
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new SQLException("No ha devuelto nada la consulta "
                        + "a la tabla" + " 'Envia'");
            }

            return resultSet.getDouble(1);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findCost

    public List<ShippingVO> findRates(Connection connection,
            String idSeller, String country, int startIndex,
            int count) throws InternalErrorException {
        /* devuelve los precios de envio de un vendedor a un pais */

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT peso, precio "
                    + "FROM envia WHERE id_vendedor = ? "
                    + "AND id_pais = ?";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);


            preparedStatement.setString(1, idSeller);
            preparedStatement.setString(2, country);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<ShippingVO> shippingVOs = new ArrayList<ShippingVO>();
            int currentCount = 0;

            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
                do {
                    int i = 1;
                    double peso = resultSet.getDouble(i++);
                    double precio = resultSet.getDouble(i++);


                    shippingVOs.add(new ShippingVO(idSeller, country,
                            peso, precio));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));
            }//if

            // Return value objects.
            return shippingVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findRates

    public void update(Connection connection, ShippingVO shippingVO)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        System.out.println(shippingVO);

        try {

            // Create "preparedStatement".
            String queryString = "UPDATE envia SET precio = ? "
                    + "WHERE id_vendedor = ? AND id_pais = ? "
                    + "AND peso = ?";

            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setDouble(i++, shippingVO.getPrecio());
            preparedStatement.setString(i++, shippingVO.getIdVendedor());
            preparedStatement.setString(i++, shippingVO.getIdPais());
            preparedStatement.setDouble(i++, shippingVO.getPeso());

            // Execute query. 
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(
                        shippingVO.getIdVendedor(),
                        ShippingVO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Fila duplicada para el id_vendedor = '"
                        + shippingVO.getIdVendedor() + " ,id_pais = '"
                        + shippingVO.getIdPais() + " ,peso = '"
                        + shippingVO.getPeso() + "' en la tabla 'Envia'");
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//update

    public void remove(Connection connection, String idVendedor, String idPais)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement".
            String queryString = "DELETE FROM envia WHERE "
                    + "id_vendedor = ? AND id_pais = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setString(i++, idVendedor);
            preparedStatement.setString(i++, idPais);


            // Execute query.
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(idVendedor,
                        ShippingVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//remove

    public void remove(Connection connection, ShippingVO shippingVO)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement". 
            String queryString = "DELETE FROM envia WHERE "
                    + "id_vendedor = ? AND id_pais = ? AND peso = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setString(i++, shippingVO.getIdVendedor());
            preparedStatement.setString(i++, shippingVO.getIdPais());
            preparedStatement.setDouble(i++, shippingVO.getPeso());

            // Execute query. 
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(shippingVO.getIdVendedor(),
                        ShippingVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//remove

    public boolean exists(Connection connection, ShippingVO shippingVO) throws InternalErrorException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
